<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Search_model extends CI_Model {


	public function searchGuardian($qString){
		$val = $this->db->escape_like_str($qString);
		$sql  = "SELECT g.guardian_id, ";
		$sql .= "CONCAT_WS(\" \", g.first_name, g.middle_name, g.last_name) AS `whole_name`, ";
		$sql .= "IFNULL(sub_s.total,0) AS `total_students` ";
		$sql .= "FROM `guardian` g ";
		$sql .= "LEFT JOIN (";
		$sql .= "SELECT COUNT(*) total,guardian_id ";
		$sql .= "FROM student ";
		$sql .= "GROUP BY guardian_id ";
		$sql .= ") sub_s ";
		$sql .= "ON (sub_s.guardian_id = g.guardian_id) ";
		if(strlen($val)>0){
			$sql .= "WHERE g.last_name LIKE '%".$val."%' ";
			$sql .= "OR g.first_name LIKE '%".$val."%' ";
			$sql .= "OR g.middle_name LIKE '%".$val."%'";
		}		
		
		$query = $this->db->query($sql);
		//return $sql;
		return $query->result_array();
	}


	public function searchStudent($name="",$id="",$sy=""){
	

		$nameVal = $this->db->escape_like_str($name);
		$idVal = $this->db->escape_like_str($id);
		$syVal = $this->db->escape_like_str($sy);

		$sql  = "SELECT s.student_id, ";
		$sql .= "CONCAT_WS(\" \", s.first_name, s.middle_name, s.last_name) AS `full_name` ";
		$sql .= ",g.guardian_name,s.year_level,s.school_year,s.last_updated ";
		$sql .= "FROM student s ";
		$sql .= "LEFT JOIN (";
		$sql .= "SELECT guardian_id,account_id,CONCAT_WS(\" \", first_name, middle_name, last_name) AS `guardian_name` ";
    	$sql .= "FROM guardian ";
		$sql .= "GROUP BY guardian_id ";
		$sql .= ") g ON (g.guardian_id=s.guardian_id) ";
		
			
		
		
		
		if(strlen($nameVal)>0){
			$sql .= "WHERE (s.last_name LIKE '%".$nameVal."%' ";
			$sql .= "OR s.first_name LIKE '%".$nameVal."%' ";
			$sql .= "OR s.middle_name LIKE '%".$nameVal."%') ";
		}

		if(strlen($idVal)>0){
			$sql .= (strpos($sql, 'WHERE') !== FALSE)?"AND ":"WHERE ";
			$sql .= "(s.student_id ='".$idVal."') ";
		}

		if(strlen($syVal)>0){
			$sql .= (strpos($sql, 'WHERE') !== FALSE)?"AND ":"WHERE ";
			$sql .= "(s.school_year LIKE '%".$syVal."%') ";
		}
		
		if($this->session->userdata('account_id')!=null && $this->session->userdata('userlevel')!=null){
			if($this->session->userdata('userlevel')=='0'){
				$sql .= (strpos($sql, 'WHERE') !== FALSE)?"AND ":"WHERE ";
				$sql .= "(g.account_id = '".$this->session->userdata('account_id')."') ";
			}
		}
		
		//$sql = "SELECT * FROM `guardian` WHERE `last_name` LIKE '%?%' OR `first_name` LIKE '%?%' OR `middle_name` LIKE '%?%'";
		$query = $this->db->query($sql);
		//$query = $this->db->query($sql,array($val,$val,$val));
		//return $sql;
		return $query->result_array();
	}

	public function searchStudentByID($id=-1){
		$cleanedID = $this->db->escape_str($id);
		$sql  = "SELECT * ";
		$sql .= "FROM student ";
		$sql .= "WHERE student_id='".$cleanedID."'";
		$query = $this->db->query($sql);
		//$query = $this->db->query($sql,array($val,$val,$val));
		//return $sql;
		return $query->result_array();		
	}
	
	public function getGuardianID($acctID=-1){
		$cleanedID = $this->db->escape_str($acctID);
		$sql  = "SELECT guardian_id ";
		$sql .= "FROM guardian ";
		$sql .= "WHERE account_id='".$cleanedID."'";
		$query = $this->db->query($sql);
		//$query = $this->db->query($sql,array($val,$val,$val));
		//return $sql;
		return $query->result_array();	
	}
	
	

	public function searchStudentByGuardianID($id=-1){
		$cleanedID = $this->db->escape_str($id);
		$sql  = "SELECT * ";
		$sql .= "FROM student ";
		$sql .= "WHERE guardian_id='".$cleanedID."'";
		$query = $this->db->query($sql);
		//$query = $this->db->query($sql,array($val,$val,$val));
		//return $sql;
		return $query->result_array();		
	}


	public function searchGuardianByID($id=-1){
		$cleanedID = $this->db->escape_str($id);
		$sql  = "SELECT * ";
		$sql .= "FROM guardian ";
		$sql .= "WHERE guardian_id='".$cleanedID."'";
		$query = $this->db->query($sql);
		//$query = $this->db->query($sql,array($val,$val,$val));
		//return $sql;
		return $query->result_array();		
	}
	
	
}

/* End of file welcome.php */
/* Location: ./application/controllers/welcome.php */